package com.friday.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import com.friday.util.DBUtil;
import com.friday.dao.EvectionApplyDao;
import com.friday.po.EvectionApply;
import java.util.UUID;

public class EvectionApplyDaoImpl implements EvectionApplyDao {
	private Connection conn = null;
	private Statement stmt = null;
	private PreparedStatement pStmt = null;
	private ResultSet rs = null;
	private DBUtil util = null;
	
	@Override
	public int insertEvectionApply(EvectionApply eve) throws Exception {
		String sql = "insert into evectionapply values(?,?,?,?,?,?,?,?)";
		int num;
		
		try {
			util = DBUtil.newInstance();
			conn = util.getConnection();
			conn.setAutoCommit(false);
			
			pStmt = conn.prepareStatement(sql);
			pStmt.setString(1, eve.getId());
			pStmt.setString(2, eve.getEmployee());
			pStmt.setString(3, eve.getDepartment());
			pStmt.setDate(4, eve.getApplyDate());
			pStmt.setDate(5, eve.getStartDate());
			pStmt.setDate(6, eve.getEndDate());
			pStmt.setInt(7, eve.getState());
			pStmt.setString(8, eve.getNote());
			
			num = pStmt.executeUpdate();
			conn.commit();
			
		} catch (Exception e) {
			conn.rollback();
			throw new Exception("evectionapplydao insert error!");
		}finally {
			util.close(pStmt);
			util.close(conn);
		}
		
		return num;
	}

	@Override
	public int updateDifferenceById(EvectionApply eve) throws Exception {
		String sql = "update evectionapply set employee = ? , department = ?, applydate = ? , startdate = ? , enddate = ? ,state = ? , note = ? where id = ?";
		int num;
		
		try {
			util = DBUtil.newInstance();
			conn = util.getConnection();
			conn.setAutoCommit(false);
			
			pStmt = conn.prepareStatement(sql);
			
			pStmt.setString(1, eve.getEmployee());
			pStmt.setString(2, eve.getDepartment());
			pStmt.setDate(3, eve.getApplyDate());
			pStmt.setDate(4, eve.getStartDate());
			pStmt.setDate(5, eve.getEndDate());
			pStmt.setInt(6, eve.getState());
			pStmt.setString(7, eve.getNote());
			pStmt.setString(8, eve.getId());
			
			num = pStmt.executeUpdate();
			conn.commit();
			
		} catch (Exception e) {
			conn.rollback();
			throw new Exception("evectionApplydao update error!");
		}finally {
			util.close(pStmt);
			util.close(conn);
		}
		
		return num;
	}

	@Override
	public int deletebyId(String id) throws Exception {
		String sql = " delete from evectionapply where id = ?";
		int num = 0;
		
		try {
			util = DBUtil.newInstance();
			conn = util.getConnection();
			conn.setAutoCommit(false);
			pStmt = conn.prepareStatement(sql);
			
			pStmt.setString(1, id);
			
			num = pStmt.executeUpdate();
			conn.commit();
			
		} catch (Exception e) {
			conn.rollback();
			throw new Exception("evectionApplydao delete error!");
		}finally{
			util.close(pStmt);
			util.close(conn);
		}
		
		
		return num;
	}

	@Override
	public EvectionApply querybyId(String id) throws Exception {
		String sql = "select * from evectionapply where id = '"+ id+"'";
		EvectionApply eve = new EvectionApply();
		
		util = DBUtil.newInstance();
		conn = util.getConnection();
		stmt = conn.createStatement();
		
		rs = stmt.executeQuery(sql);
		
		try {
			while(rs.next())
			{
				eve.setApplyDate(rs.getDate("applyDate"));
				eve.setDepartment(rs.getString("department"));
				eve.setEmployee(rs.getString("employee"));
				eve.setEndDate(rs.getDate("endDate"));
				eve.setId(rs.getString("id"));
				eve.setNote(rs.getString("note"));
				eve.setStartDate(rs.getDate("startDate"));
				eve.setState(rs.getInt("state"));
			}
		} catch (Exception e) {
			throw new Exception("evectionApply querybyid error!");
		}finally{
			util.close(rs);
			util.close(stmt);
			util.close(conn);
		}
		
		return eve;
	}
	
	@Override
	public List<EvectionApply> querybyEmployee(String employee) throws Exception{
		String sql = "select * from evectionapply where employee = '" + employee + "' order by state";
		List<EvectionApply> list = new ArrayList<EvectionApply>();
		list = this.querybysql(sql);
		return list;
	}
	
	@Override
	public List<EvectionApply> querybyState(int state) throws Exception {
		String sql = "select * from evectionapply where state = " + state + " order by state";
		List<EvectionApply> list = new ArrayList<EvectionApply>();
		list = this.querybysql(sql);

		return list;
	}
	
	@Override
	public List<EvectionApply> querybyEmployeeandState(String employee,int state) throws Exception
	{
		String sql = "select * from evectionapply where employee = '" + "'" +" and state = "+ state + " order by state";
		List<EvectionApply> list = new ArrayList<EvectionApply>();
		list = this.querybysql(sql);

		return list;
	}
	
	@Override
	public List<EvectionApply> querybyDepartment(String department) throws Exception
	{
		String sql = "select * from evectionapply where department = '" + department + "' order by state";
		List<EvectionApply> list = new ArrayList<EvectionApply>();
		list = this.querybysql(sql);
		return list;
	}
	
	@Override
	public List<EvectionApply> querybyDepartmentandState(String department,int state) throws Exception
	{
		
		String sql = "select * from evectionapply where department = '"+ department + "'" +" and state = "+state+" order by state";
		List<EvectionApply> list = new ArrayList<EvectionApply>();
		list = this.querybysql(sql);
		
		return list;
	}
	
	@Override
	public List<EvectionApply> queryAll() throws Exception {
		String sql = "select * from evectionapply order by state";
		List<EvectionApply> list = new ArrayList<EvectionApply>();
		list = this.querybysql(sql);
		return list;
	}
	
	private List<EvectionApply> querybysql(String sql) throws Exception {
		List<EvectionApply> list = new ArrayList<EvectionApply>();
		
		try {
			util = DBUtil.newInstance();
			conn = util.getConnection();
			stmt = conn.createStatement();
			
			rs = stmt.executeQuery(sql);
			
			while(rs.next())
			{
				EvectionApply eve = new EvectionApply();
				
				eve.setApplyDate(rs.getDate("applyDate"));
				eve.setDepartment(rs.getString("department"));
				eve.setEmployee(rs.getString("employee"));
				eve.setEndDate(rs.getDate("endDate"));
				eve.setId(rs.getString("id"));
				eve.setNote(rs.getString("note"));
				eve.setStartDate(rs.getDate("startDate"));
				eve.setState(rs.getInt("state"));
				
				list.add(eve);
			}
		} catch (Exception e) {
			throw new Exception("evectionapplydao query error!");
		}finally{
			util.close(rs);
			util.close(stmt);
			util.close(conn);
		}
		return list;
	}

}
